C:/Projekte/RAIS/MigrationTool/Table.cs

Go to the documentation of this file.

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.OleDb;


namespace RAISInstall
{
    public class Table:IDisposable
    {
        SqlConnection sqlConnection;
        OleDbConnection accessConnection;
        DbDataReader accessReader;
        string[] names ;
        Type[] types;

        string _sourceTableName;
        string _destTableName;
        string createTableCommand = "create Table {0}({1})";
        string dropTableCommand = "IF EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND name = '{0}') " +
                                "BEGIN " +
                                    "DROP  Table [{0}] " +
                                "END ";

        string insertCommand="INSERT into {0}({1}) values({2})";

        public Table(string accessConnectionString, string sqlConnectionString)
        {
            sqlConnection = new SqlConnection(sqlConnectionString);
            accessConnection = new OleDbConnection(accessConnectionString);
            sqlConnection.Open();
            accessConnection.Open();
        }

        private void Drop()
        {
            DbCommand toCommand = new SqlCommand(string.Format(dropTableCommand, _destTableName), sqlConnection);
            toCommand.ExecuteNonQuery();
        }

        private void Create()
        {
            StringBuilder body=new StringBuilder();
            for (int i = 0; i < names.Length; i++)
            {
                body.Append("[");
                body.Append(names[i]);
                body.Append("] ");
                
                body.Append(GetSQLType(types[i]));
                if(names.Length-1!=i)
                    body.Append(",");
            }
            DbCommand toCommand = new SqlCommand(string.Format(createTableCommand,_destTableName, body.ToString()), sqlConnection);
            toCommand.ExecuteNonQuery();
        }

        public string GetSQLType(Type type)
        { 
            switch(type.Name)
            {
                case "Int32":
                    return "int";
                case "String":
                    return "[nvarchar](max)";
                case "Boolean":
                    return "tinyint";
                default:
                    break;
            }
            return "";
        }

        public void Copy(string sourceTable, string destTable)
        {
            _sourceTableName = sourceTable;
            _destTableName = destTable;
            DbCommand accessCommand = new OleDbCommand(string.Format("select * from [{0}]", _sourceTableName), accessConnection);
            accessReader = accessCommand.ExecuteReader();
            names = new string[accessReader.FieldCount];
            types = new Type[accessReader.FieldCount];
            
            for (int i = 0; i < accessReader.FieldCount; i++)
            {
                names[i] = accessReader.GetName(i);
                types[i] = accessReader.GetFieldType(i);
            }
            Drop();
            Create();
            DataCopy();
        }

        private void DataCopy()
        {
            StringBuilder fields = new StringBuilder();
            for (int i = 0; i < names.Length; i++)
            {
                fields.Append("[");
                fields.Append(names[i]);
                fields.Append("]");
                if (names.Length - 1 != i)
                    fields.Append(",");
            }

            StringBuilder values = new StringBuilder();
            DbCommand toCommand = new SqlCommand();
            toCommand.Connection = sqlConnection;

            while (accessReader.Read())
            {
                for (int i = 0; i < types.Length; i++)
                {
                    if (accessReader[i] is DBNull)
                        values.Append("null");
                    else
                    {
                        if (types[i].Name == "String")
                            AppendString(values, accessReader[i]);                            
                        else if (types[i].Name == "Boolean")
                            if (((Boolean)accessReader[i]))
                                values.Append("1");
                            else
                                values.Append("0");
                        else
                            values.Append(accessReader[i].ToString());
                    }
                    if (names.Length - 1 != i)
                        values.Append(",");
                }
                toCommand.CommandText = string.Format(insertCommand, _destTableName, fields.ToString(), values.ToString());
                toCommand.ExecuteNonQuery();
                values = new StringBuilder();
            }
        }

        private void AppendString(StringBuilder sb, object o )
        {
            sb.Append("N'");
            sb.Append(o.ToString().Replace("'","''"));
            sb.Append("'");
        }
        

        public void Dispose()
        {
            sqlConnection.Close();
            accessConnection.Close();
        }
    }
}